Unsplash

Introduction

This resource aims to show you some of the more advanced features of Microsoft Excel Version 2006 for Microsoft 365.  It is assumed you already know how to do the following:

• Enter numbers and text into cells
• Enter formula into cells for automatic calculation
• Change the format of cells to improve appearance, e.g. number of decimal places, centering, using scientific notation, etc.
• Change the page format to improve appearance, e.g. adding headers and footers, inserting images, using styles, changing colours, etc.
• Add and delete worksheets, rows and columns. Rename worksheets. 

The really powerful features of Excel are its mathematical, data analysis, and graphing capabilities, so this resource will introduce you to some of these.  On a personal note, I used Excel, way back in the 1990s, to produce a radar model for accurately predicting target signal size, so it is no exaggeration to say that it is a key software tool for any engineer's toolbox.  

We will examine these features using examples and we hope you find this a useful resource to help you analyse, evaluate and present data and calculations for your coursework and project reports. 

Relative and Absolute References

You probably already know how to write formulae in Excel using the '=' sign, and how to copy and paste from one (or a group) of cells to another, but you might not know that the two types of cell references used, sometimes determine how data is copied. 

A) The Excel default is relative referencing, with cells addressed by their column and row references, e.g. A4, C7, GK3056, etc.  When you copy these cells, to use in formula (by left clicking or by typing the column/row reference), Excel will automatically change the 'addresses'.  For example, if we have data in Figure 1:

Figure 1

The answer appearing in cell C1 will be 10 x 100 = 1000.

Then if we copy cell C1 to cell C4, the answer appearing in cell C4 will be 13 x 400 = 5200, because the relative cell references will change from A1 to A4 and from B1 to B4 (to match C4).

If we copy cell C1 to cell D1, the answer appearing in cell D1 will be 100 x 1000 = 100000, because the relative cell references will change from A1 to B1 and from B1 to C1 (to match cell D1).  

This may seem quite annoying, but actually it is really useful if we have a list of variables that we want to do the same calculations on.  For example, assume we want to find the instantaneous amplitude of a sine wave at various angles specified in degrees, and we also want to see the angles expressed in radians. We would just need to write the formula to change degrees to radians in one cell and the sine wave formula in another (see Figure 2), then we could select and drag the little box at the bottom right of the cell, to get all the answers in every other cell we want. 

 B) An absolute reference is defined by adding a $ sign to either the column reference, the row reference, or both, e.g. $A$5, $D7, F$23.  The reference just after the dollar sign will not change when you copy the cell contents to another cell.  This is quite useful if you want to define some constants.  For example, in the sine wave example above, say we wanted to add a constant phase shift (0.236 radians) to every answer in column C.  We could define this phase shift in another cell, say cell E1 (see Figure 3), and make this an absolute reference by either typing the dollar signs after we had copied the reference 'address', or by cycling through the F4 key to get $E$1.

Figure 3

So then copying cell C2 to cell C4 will give = 5*SIN(B4+$E$1) (which will, of course, give an invalid result, if cell B4 hasn't been calculated). 

Sorting, Filtering and Searching Data

Sorting, filtering and searching data are all key requirements for any database, and the starting point for data analysis.  Click the headings below to expand.

Sorting Data

For example, suppose we had this data, in Figure 4 : 

	A	B
	Item Number	Item Value
1	5	6.8
2	3	2.8
3	1	3.7
4	2	7.2
5	4	4.5
6	0	3.6

Figure 4

Figure 4 is not very useful for quick glance analysis, or for graphing, and we might prefer to have the data sorted in items 0 to 5, or 5 to 0 or by value low to high, or value high to low.  This is very easy to do in Excel.  Just highlight the data you want to sort, then click on Data: Sort (also accessible from the Home menu, under Sort & Filter).  If you have only selected 1 column, Excel will ask if you want to 'Expand the selection'.  Usually you would want to say yes, because if not, only the selected column would be sorted. 

Question

If we sort the data in Figure 4, by column A = Item Number, smallest to largest, without selecting column B and not selecting 'Expand the selection' when asked, what would be the data in cell B2? 

Reveal

The data would still be 2.8 as this column would not be sorted, even though cell A2 would read 1, as sorted in order A1 = 0, A2 = 1, A3 = 2, etc. 

Question

What would be the data in cell B2 if we select both columns A and B (or choose 'Expand the selection' when asked) before sorting by Item Number, Cell Value and Smallest to Largest, as shown?

Reveal

The data would be 3.7 because cell A2 would be 1, and the data in column B would correspond with sorted column A: 0, 1, 2, 3, etc.  

Data can be sorted in columns or rows in:

• numerical order: smallest to largest or largest to smallest
• alphabetical order: A to Z or Z to A
• cell colours (order is limited to selecting top and bottom colours)
• font colours (order is limited to selecting top and bottom colours)
• a conditional format you have specified (beyond the scope of these notes, but useful for determining data trends (see the 44 second video at LinkedIn Learning: Excel Conditional Formatting).

There is even hierarchical sorting, where you can sort by one parameter (e.g. cell colour) and then another (e.g. A to Z) for cells which have the same initial properties.  For example, if we coloured the cells in Figure 4, and then sorted, first by colour in column A = Item Number, then by Smallest to Largest in column B = Item Value, we could get:

Filtering Data

We can filter Excel data, so that only limited data sets are seen.  This is used quite a lot for financial data, but as an example consider Figure 5 showing a fictitious group of students and their grades in a test. The data has been manually colour coded.  We can filter the data by colour to only show those students who have achieved distincition in this assignment, by selecting the data, then clicking Data: Filter.  Drop down arrows then appear in each column which give options of filtering by Name (e.g. to find a single student and their grade, in a long list),  Grade (e.g. to identify which student achieved a specific mark), Color, or by using various Number filters.  Filtering by Grade: Color: Green gives us the table shown in Figure 6.  The arrows in cells A1 and B1 give options of clearing the filter, selecting new filters and sorting the data, and the little funnel sign in B1 shows that data is filtered by this column.  All filters can be cancelled by just selecting the Filter button again. 

The Filtering menu in Excel also allow you to sort the data, as we discussed earlier. 

Question

If you filter the data in Figure 5 by Number Filters: Grade: Less Than: is less than 60, then sort by Grade: largest to smallest, whose name will be at the top of the list?  

Reveal

Jo.  Filtering by Grade 'is less than 60' will display four results for Jo, Fatima, Ming Mei and Lesley, then sorting by Grade largest to smallest will put Jo at the top of the list, as this fictitious student achieved a better mark (56) than the other 3 students.   Notice that filtering and sorting this way, automatically adjusts all the associated data, not just the column you are filtering and sorting by.  

 

Using Tables for Filtering

Another way to apply filters is to turn our data into a table first, by selecting the data and clicking Insert: Table from the Home menu. If you check the box 'My table has headers' this will automatically add options (selectable from arrows, as before) to choose filters from each column heading or to sort data.  When using tables, all the data in the table is automatically selected for filtering and sorting.  Tables can be formatted in the Table Design menu - for style, size, first row and first column highlighting, etc. 

Although there are text filters which allow you to pick specific text, the text sorting features are limited to A to Z and Z to A.  This can be a problem if you want to sort by, for example month, but there are some additional options if you select a cell in the column you want to sort and choose Sort & Filter: Custom Sort: Order: Custom Lists (you can create your own Custom List here as well). 

 

Searching Data

Excel has some fairly powerful search functions, such as VLOOKUP and INDEX and MATCH. They can be a little tricky to understand, so we will just explain VLOOKUP, using an example, in these notes. If you want to learn more, you can watch the 10 minute video at LinkedIn Learning: Using VLOOKUP, MATCH AND INDEX.  The big disadvantage of VLOOKUP is that the value you want to look up, must be in the first column of the data for lookup, and the values in this column must be sorted in ascending order (smallest to largest).  INDEX and MATCH allow look up in any column. 

Example: Figure 7 shows measured Torque and Power values for a motor running at various Speeds: 

Let's assume we want to know what torque is developed when the motor is running at 30 rpm. We could type into a cell, the formula =VLOOKUP(30, $A$1:$C$26,2,FALSE) and this would correctly return the answer 0.408 (Nm).

• 30 is the number we are looking up (this could, of course, be a cell reference to the number instead, e.g. if cell D1 contained the speed we wanted to look up (30), this would be =VLOOKUP(D1,$A$1:$C$26,2,FALSE)).
• $A$1:$C$26 defines the rows and columns we are looking the data up, in. We have used absolute references because this data is never going to move, but of course, these could equally have been relative references which would track the data's location if we changed it.
• 2 defines the column we want to look for the matching torque in, in this case column B which is the second column of the selected data. If we wanted to find the power which was developed when the motor was running at 30 rpm, this would be 3, representing column C, or the third column of the data.
• FALSE indicates that we are looking for an exact match, i.e. exactly 30, not 30.1 or 29.9. If you type TRUE here, Excel will look for the closest match, so for example =VLOOKUP(90,A1:C26,3,TRUE) would return 0.23, as 91 is the closest match to 90, and the power (column 3) at 91 rpm, is 0.23 N-m/s.  Note that TRUE only works with numerical data in the first column, sorted from smallest to largest. 

Question

What value would be returned if we typed the formula =VLOOKUP(20,A1:C26,2,TRUE)?

Reveal

0.48 (Nm) as this is the Torque (tcolumn 2) which matches speed = 19 rpm, the closest match to 20. Note that if you typed FALSE instead of TRUE you would get a N/A answer because there would be no exact match to 20. This can be a bit of a nusiance if you have decimal places and you don't type in exactly the correct match.

VLOOKUP can also be used to look up words. These need to be enclosed in quotation marks, e.g. VLOOKUP("Highlands", A1:C3, 2, FALSE). The matches are not generally case sensitive (e.g. "highlands" would also work), but only exact matches (FALSE) will work properly.

If the VLOOKUP value occurs more than once in the first column, Excel will stop at the first match, and return only the value associated with this number (or text). 

#N/A Errors

If VLOOKUP is giving you #N/A errors, the cause is likely to be one of these reasons:

a) The data you are trying to find is not actually in the first column.  Try changing the match from FALSE to TRUE.

b) The data in the first column is not sorted in ascending order (smallest to largest).  Try sorting the first column data.

c) The data in the first column is not in the same format as the search data (e.g. text instead of number).  Try reformatting the first column data. 

d) There are some additional characters in the first column data, e.g. spaces.  If you are looking up text, you could fix this by using the Excel wildcard *, for example =VLOOKUP("Diode*",A1:D5,2,FALSE) will find all the words starting with Diode, in the first column of your data table (e.g, Diode Rectifier, Diode Signal, Diode Power could all be found).  Of course, if the word occurs more than once in the column Excel will only return the answer for the first found. 

Presenting and Analysing Data

This next section covers some of the most useful features of Excel for engineering:

• Conditional Formatting to highlight specific values or text to improve presentation for future analysis.
• Using Statistical Functions to analyse data.
• Using Graphs to analyse data.

It is not intended to be a detailed guide to Excel, rather just to show you some of the things it is capable of, so you can go away and explore these further for yourself. 

Conditional Formatting

The first action we will look at is Conditional Formatting, accessed by selecting the data to be formatted and, from the Home menu, choosing Conditional Formatting.  This allows the reader to do quick visual analysis of trends, and cells with conditional formats can also be quickly found using Find & Select: Conditional Formatting (on the Home menu): .  The menu items in Conditional Formatting are:

Example using Custom Rules 

Figure 8 shows the mean temperature per month, from 2010 to 2019, for a fictitious northern hemisphere country (the data was generated using Excel's =RANDOMBETWEEN() function). 

If we run a market garden, we might be interested in highlighting whenever the temperature goes above or below specific values, e.g. >19 ^oC or < -5 ^oC.  There is no pre-defined rule which can do this.  In Figure 8 these limits have been typed into cells B16 and B17, although they could just be entered as values in the formula, but this doesn't allow a reader to see or change them easily.  Selecting cells B3 to K14, we can set up Conditional Formatting: New Rule : Use a formula to determine which cells to format.  The formula will be =OR(B3< $B$16,B3>$B$17).  B3 is the top left cell of our selected data (white cell) and needs to have a relative reference, $B$16 defines the cell (fixed, so absolute reference) which contains the minimum temperature and $B$17 contains the maximum temperature, both of which can be assumed to be constant, so using absolute references.  We can also use the Format button (in the New Formatting Rule pop up box) to format the highlighter to make these cells fill pale orange.  The result of doing this is:

The sheet is dynamic, so if we change any values it will automatically update the highlighting.  Note that you can turn off automatic updating by selecting Formulas: Calculation Options: Manual, but this is rarely done as updates then need to be initiated by Calculate Now or Calculate Sheet.  

Using Statistical Functions

Doing statistical calculations on Exel spreadsheets is so common that Excel have included a quick menu button on the Home menu. The choices here are:

	This adds all the values in the cell array (row) to the left of it, or all the values in the cells cell array (column) above it.  This menu also gives access to all the Excel functions, under More Functions.
Average	This finds the average of all the values in the cell array (row) to the left of it, or the average of all the values in the cell array (column) above it.
Count Numbers	This tells you the number of cells with numerical values in the cell array (row) to the left of it, or in the cell array (column) above it.
Max or Min	This returns the maximum or minimum numerical value in the cell array (row) to the left of it, or in the cell array (column) above it.

When you use these, check which cells are being used for data, as these are dynamic and can change if the function moves to a different cell. Alternatively, you can select the data first, and on clicking these, the answer will appear in the next available column (if row data) or row (if column data). 

There are many more functions which can be used for statistical analysis, for example MEDIAN(), mode = MODE,MULT() or MODE.SNGL(), sum of squares = SUMSQ(), sum of products = SUMPRODUCT(),  a whole range of standard deviation options =STDEV.P(), STDEV.S(), etc.  Most of these can be found in Formulas: Math & Trig menu or Formulas: More Functions: Statistical.  To find out more about these, consult Excel's own help or watch the videos at LinkedIn Learning: Excel Statistics Essential Training 1 (this is a 3.5 hour course but each video is only about 5 minutes long and you can fast forward through them.  The most useful starting one is probably the third video: LinkedIn Learning: Excel Statistical Functions).

It is extremely easy to graph data using Excel, but what can be rather bewildering are the number of graph options available, from bar charts to scatter charts to pie charts, etc.  We are going to concentrate on the most common ones used in engineering and explain why and how they are used to analyse data. 

Scatter Chart

Perhaps the most common data analysis done by engineers is comparing one set of values with another, for example: speed versus time, pressure versus force, current versus voltage, etc. to determine the relationship between them.  The best type of chart to use for this is the Scatter Chart, and it is good practice to sort the x axis data in ascending order before plotting.  Then the easiest way to create a Scatter Chart is to select the x axis data then press CTRL and select the y axis data (or if columns are consecutive, this can be done by select and drag), then Insert: Charts: Scatter (additional y axis series data can be added with CTRL held down). 

There are a few options with the Scatter Chart: charts showing or hiding data points, charts with smooth joins between data points shown, charts with straight joins between data points shown.  To decide which to use consider what analysis you are trying to show.  If you are just interested in the actual data, with no assumptions being made, then a chart without join lines is best (see Figure 9), however, if you want to show assumed trends betwen each data point (which is very common with Scatter Chart analysis) you will want to add joining lines - linear variations best suit straight lines, non-linear variations suit smooth lines.

Always ensure you label your charts well, with a clear title, axes labels with Standard International (SI) units of measurement and a key (if you have more than one set of y axis values on the same chart).  If you want to plot two sets of data with very different ranges, on the same chart, you can use a secondary axis, which will appear on the right hand side of the chart. 

You can format most chart features by selecting which aspect you wish to change (e.g. chart area, x axis, y axis etc.), right clicking and selecting 'Format....'.  Quick access formatting menus also appear on the right of the chart.  The menu options are quite self-explanatory, and generally just improve appearance, not analysis, so you can try these for yourself. 

Chart Design: Add Chart Element allows you to format the chart for:

Axes	Add or edit the axes values (these should always be shown)
Axes Titles	Add or edit the axes titles
Chart Title	Add or edit the chart title
Data Labels	Show each data point's value on the graph
Gridlines	Turn on or off x and y axes gridlines
Legend	Add or edit the data key (legend)

Scatter Charts can only have numerical or date values on the x axis, but this is the only type of chart where both the x and y axes can be formatted to be non-linear, for example logarithmic.  Figure 10 shows a chart of amplifier gain, versus frequency, where the x axis has been changed from linear to logarithmic by selecting it, right clicking to Format axis, and then selecting Logarithmic scale (Base 10 is most common). 

Line Chart

Line Charts are also quite common in engineering, to show trends of data over longer periods of time or over categories.  The main difference between line and Scatter Charts is that Line Charts can have x axis values which aren't numeric, but the x axis values must be sorted and linear, with the same interval between each value.  If you don't select x axis values for a line chart, Excel will use it's own row numbers.  Figure 11 shows the same data as was plotted in Figure 9.  You can clearly see that all three columns of data (Speed, Torque and Power) are plotted on the y axis, with the x axis being the Excel row numbers, which is definitely not what you would want to analyse how power and torque varied with speed.  

Figure 12 shows total energy consumption, per year, in the UK, for which the Line Chart is more suited.  Be careful with Line Charts though - Line Charts always show trends between data points (these can be removed in Scatter Charts) and these are assumed rather than actual; for example in Figure 11, if the motor had been sticky and slowed momentarily between measurements, the graph is wrong as it shows a totally linear response.  

Secondary axes cannot be added to Line Charts. They need to be changed to Combo Charts (Chart Design:Change Chart Type), and then the data requiring the secondary axes can be selected.  Combo Charts allow you to use different plot types for each y axis data series, but this is not recommended as can make the chart difficult to read. 

Column Chart

Column Charts (and the horizontal equivalent, Bar Charts) are useful when y axis data is fixed over a time or category period, for example the number of people of a certain age, the average wind speed over a week, quantities of different size screws, etc. Column Charts are best used when there aren't too many y axis series to be displayed, as they can appear cluttered very quickly.  Excel has a big range of Column and Bar Charts to choose from, but they are not fundamentally different, so just pick whichever looks best.  Figure 13 shows a very basic Column Chart of the fictitious student grades from Figure 5.  However, Figure 13 is not very pleasing to look at, and also difficult to ascertain any trends from.  So this is better as a Column Chart with the grades sorted in descending numerical order, as shown in Figure 14, and with formatting changes done through the right hand formatting pane (formatting columns even allows the gaps between them to be reduced). 

Pie Chart

Pie Charts are a very visual way of showing percentages, of a whole = 100%.  They are used a lot in business presentations. For example, Figure 15 shows the amount spent on kitting out a workshop, and Figure 16 shows the total cost per item type as a percentage of the total spent, as a Pie Chart.  Note that a good Pie Chart will usually have the quantities sorted in ascending or descending order, before plotting.  As with all the previous charts, Pie Charts can be formatted to look good (select element and right click), and data labels can be added if you want the viewer to see the original worksheet numbers. 

Graphing Data: Common Features

Select Data Source

This pop up box can be opened by selecting the chart, right clicking and choosing Select Data.  

Trend Lines

Overall trend lines can be added to your scatter and line graphs by selecting the data in the graph that you wish to analyse, right clicking and selecting Add Trendline.  This can be a very useful feature for determining the equation of a polynomial plot (e.g. y = ax²+bx+c), if you select 'Display Equation on Chart'.  The polynomial order should be picked, which gives the best trendline match.  Of course, if you have a linear graph, you can get its equation from a trendline as well, but it is probably just as easy to determine the y axis crossing point (c) and graph slope (m) yourself, to get an equation in the form y = mx + c.  There are also options for other trendlines, such as exponential and logarithmic.  Figure 17 shows a linear trendline and equation for Motor Torque and a 2nd order polynomial trendline and equation for Motor Power, as plotted in Figure 9. 

Using Pivot Tables

Pivot tables are arguably the most advanced form of data analysis you can do with Excel.  They are used to statistically analyse and present 'slices' of more extensive data.  Although all these analyses can be done in Excel Worksheets, Pivot Tables are designed to make it easier and to give more visually pleasing reports.  We are only going to give a very brief overview here but there are lots of good videos and notes on using pivot tables in Excel, for example: LinkedIn Learning: Pivot Tables in Depth. 

To create a pivot table, first make your Excel data into a table (as covered in Filtering), select any cell in the table, then from Insert Menu, choose PivotTable.  The data should have no empty rows or columns.  Excel can also recommend Pivot Table types, based on your data, in the Recommended PivotTables menu.  It is a good idea to place your pivot table on a new sheet.  Then, to build the table,select or click and drag options in the right hand pane.  Lets consider an example:

Figure 18 shows the weekly price of bananas from various countries, in Excel table form.   

Let's suppose we want to see the average price of bananas each week.  If we select a cell in the table, then choose Insert:PivotTable we get Figure 19.  Then selecting Country and all the weeks we want to analyse (from 10/01/20 to 28/2/20) in Pivot Table Fields, gives us Figure 20. 

Excel has automatically moved 'Country' to the Rows pane, to give row names, and has automatically summed all the numbers in each weekly column (see them in the Sum Of box).  Now getting the sum of the weekly prices is not very useful, and perhaps we would prefer to find the average weekly price.  To do this we need to select each column heading in turn, right click and choose Summarize Values by: Average.  You will see that the 'Sum of' header will have changed to 'Average of' and the calculation at the bottom, although still called Grand Total, is now actually the average (Figure 21).  Be careful to check that the data in each column is correct and filter if necessary using the right hand arrows. 

Although this might not seem very exciting, since we can easily get an average for each column in our plain worksheet, Pivot Tables only really become useful when you have a lot of data to analyse.  Because they are dynamic (change with data changing), they are a very quick way to slice, filter, analyse and present information. 

Summary

We hope you have found this a useful introduction to some of the more advanced features of Excel.  You should now practice sorting, filtering, and searching and analysing Excel data and improving your data presentation, using the techniques you have learnt here.  We have only really touched on the capabilities of this mathematical analysis and modelling tool, so if you wish to learn more, these training courses are recommended:

LinkedIn Learning Excel Online Courses

Microsoft Introduction to Data Analysis Using Excel

References

UHI (n.d.) Engineering Skills 1: Section 7 (Data Analysis Using Excel).  Inverness: UHI

UK Government (2020) National Statistics: Energy Consumption in the UK. London: UK Government

--. (2020) National Statistics: Average weekly wholesale prices of bananas by country of origin [online] Available from https://www.gov.uk/government/statistical-data-sets/banana-prices [20th July 2020]